package com.finstone.data.spider.eshop.service;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;
import java.util.concurrent.ExecutionException;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import net.sf.json.util.JSONUtils;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Component;

import com.fins.modules.utils.JsonUtils;
import com.finstone.data.spider.cvt.model.GoodsParam;
import com.finstone.data.spider.cvt.model.GoodsProp;
import com.finstone.data.spider.cvt.model.GoodsProp.PropMap;

@Component
public class EShopConvertService {

    @Autowired
    protected JdbcTemplate jdbcTempate;

    private void updateProductAndGood(Map goodsMap) {
        try {
            Integer goods_id = Integer.parseInt(goodsMap.get("goods_id").toString());
            Integer real_id = Integer.parseInt(goodsMap.get("real_id").toString());
            String goods_sn = (String) goodsMap.get("sn");
            // 属性
            Map<String, String> attrsMap = new LinkedHashMap<String, String>();
            List<Map<String, Object>> goods_attrs = this.getJdbcTempate().queryForList("SELECT name c_name,value c_value FROM  eshop_spider.goods_c_attrs WHERE category_id= '1a60e7bf-49e3-493e-8fa3-ad907226d92e'");
            for (Map<String, Object> aMap : goods_attrs) {
                attrsMap.put((String) aMap.get("c_name"), (String) aMap.get("c_value"));
            }
            // 属性值
            Map<String, String> paramsMap = new LinkedHashMap<String, String>();
            List<Map<String, Object>> goods_params = this.getJdbcTempate().queryForList(
                    "SELECT  p.*  FROM eshop_spider.goods_params p,eshop_spider.goods g WHERE p.GOODS_ID = g.ID AND g.id = ? ORDER BY p.id", goods_id);

            for (Map<String, Object> pMap : goods_params) {
                paramsMap.put((String) pMap.get("name2"), (String) pMap.get("value"));
            }

            StringBuffer buff = new StringBuffer();

            int i = 1;

            for (String a_key : attrsMap.keySet()) {
                String a_values = attrsMap.get(a_key);
                String p_value = paramsMap.get(a_key);

                String[] a_n_values = array_unique(StringUtils.split(a_values, ","));

                int idx = ArrayUtils.indexOf(a_n_values, p_value);

                if (idx == -1) {
                    i++;
                    continue;
                }

                buff.append(" p" + i + "=" + idx + ",");

                i++;
            }

            String p_sql = buff.toString();


            p_sql = p_sql.substring(0, p_sql.length() - 1);

            this.getJdbcTempate().update("update czzfcg_ss.es_goods set " + p_sql + " where goods_id = ?", real_id);

            this.getJdbcTempate().update("INSERT INTO czzfcg_ss.es_product (product_id,goods_id,NAME,sn,store,enable_store,price,cost,weight) SELECT goods_id,goods_id,NAME,sn,store,enable_store,price,cost,weight FROM czzfcg_ss.es_goods where goods_id = ?", real_id);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

//    public void convert2attrs() {
//        String goods_list_sql = "select goods_id,sn from czzfcg_ss.es_goods order by goods_id ";
//
//        List<Map<String, Object>> goodsList = this.getJdbcTempate().queryForList(goods_list_sql);
//
//        for (Map<String, Object> goodsMap : goodsList) {
//            Integer goods_id = (Integer) goodsMap.get("goods_id");
//            String goods_sn = (String) goodsMap.get("sn");
//            // 属性
//            Map<String, String> attrsMap = new LinkedHashMap<String, String>();
//            List<Map<String, Object>> goods_attrs = this.getJdbcTempate().queryForList(
//                    "SELECT " + "  c.name c_name," + "  c.value c_value " + "FROM" + "  eshop_spider.goods_c_attrs c," + "  eshop_spider.goods g "
//                            + "WHERE c.category_id = g.category_id" + "  AND c.name <> '品牌' " + "  AND c.name <> '价格' " + "  AND g.id = ? " + "ORDER BY c.id ",
//                    goods_sn);
//
//            if (goods_attrs.size() == 0)
//                continue;
//
//            for (Map<String, Object> aMap : goods_attrs) {
//                attrsMap.put((String) aMap.get("c_name"), (String) aMap.get("c_value"));
//            }
//            // 属性值
//            Map<String, String> paramsMap = new LinkedHashMap<String, String>();
//            List<Map<String, Object>> goods_params = this.getJdbcTempate().queryForList(
//                    "SELECT " + "  p.* " + "FROM" + "  eshop_spider.goods_params p," + "  eshop_spider.goods g " + "WHERE p.GOODS_ID = g.ID"
//                            + "  AND p.NAME2 <> '品牌' AND p.NAME2 <> '价格'" + "  AND g.id = ? ORDER BY p.id", goods_sn);
//
//            for (Map<String, Object> pMap : goods_params) {
//                paramsMap.put((String) pMap.get("name2"), (String) pMap.get("value"));
//            }
//
//            StringBuffer buff = new StringBuffer();
//
//            int i = 1;
//
//            for (String a_key : attrsMap.keySet()) {
//                String a_values = attrsMap.get(a_key);
//                String p_value = paramsMap.get(a_key);
//
//                String[] a_n_values = array_unique(StringUtils.split(a_values, ","));
//
//                int idx = ArrayUtils.indexOf(a_n_values, p_value);
//
//                if (idx == -1) {
//                    i++;
//                    continue;
//                }
//
//                buff.append(" p" + i + "=" + idx + ",");
//
//                i++;
//            }
//
//            String p_sql = buff.toString();
//
//            if (StringUtils.isBlank(p_sql)) {
//                continue;
//            }
//
//            p_sql = p_sql.substring(0, p_sql.length() - 1);
//
//            System.out.println(goods_id + ":" + goods_sn);
//
//            this.getJdbcTempate().update("update czzfcg_ss.es_goods set " + p_sql + " where goods_id = ?", goods_id);
//        }
//        this.getJdbcTempate().update("DELETE FROM czzfcg_ss.es_product");
//        this.getJdbcTempate().update("INSERT INTO czzfcg_ss.es_product (product_id,goods_id,NAME,sn,store,enable_store,price,cost,weight) SELECT goods_id,goods_id,NAME,sn,store,enable_store,price,cost,weight FROM czzfcg_ss.es_goods");
//    }

    public void doConvert() {
        String key = "便携式计算机";
        String val = "Lenovo笔记本";

        String sql = "SELECT b.name,b.value FROM eshop_spider.goods_category a ,eshop_spider.goods_category_attrs b WHERE a.id = b.category_id AND NAME <> '价格' AND a.NAME3 =?";

        List<Map<String, Object>> list = this.getJdbcTempate().queryForList(sql, val);

        List<GoodsProp> propsList = new ArrayList<GoodsProp>();


        if (list != null && list.size() > 0) {

            for (Map<String, Object> map : list) {
                String name = StringUtils.trim((String) map.get("name"));
                String value = StringUtils.trim((String) map.get("value"));

                String[] values = array_unique(StringUtils.split(value, ","));


                GoodsProp props = new GoodsProp();

                props.setName(name);

                for (String str : values) {
                    props.getOptionAr().add(str);
                    props.getNums().add(0);
                    props.getOptionMap().add(new PropMap(str));
                }

                props.setOptions(StringUtils.join(values, ","));

                propsList.add(props);
            }
        }
        String propsJson = "";
        try {
            propsJson = JsonUtils.toJson(propsList);
        } catch (Exception e) {
            e.printStackTrace();
        }

        String t_sql = "";

        // 产品参数
        String sql_c = "SELECT a.*,b.category_id, c.NAME3 FROM eshop_spider.goods_params a,eshop_spider.goods_category c,(SELECT MIN(id) goods_id,category_id FROM eshop_spider.goods GROUP BY category_id) b WHERE a.goods_id =b.goods_id AND c.ID=b.category_id AND a.name2 <> '品牌' and c.name3 = ? ORDER BY a.goods_id,a.id;";

        list = this.getJdbcTempate().queryForList(sql_c, val);


        Map<String, GoodsParam> pMap = new LinkedHashMap<String, GoodsParam>();

        if (list != null && list.size() > 0) {
            for (Map<String, Object> map : list) {
                String name1 = StringUtils.trim((String) map.get("name1"));
                String name2 = StringUtils.trim((String) map.get("name2"));

                GoodsParam gP = pMap.get(name1);
                if (gP == null) {
                    gP = new GoodsParam();
                    gP.setName(name1);
                    pMap.put(name1, gP);
                }
                gP.getParamList().add(new GoodsParam.SubParam(name2));
            }
        }

        String paramsJson = JsonUtils.toJson(pMap.values());


        t_sql = "update czzfcg_ss.es_goods_type set params = ? where name = ?";

        this.getJdbcTempate().update(t_sql, paramsJson, key);

        // 导入商品

        t_sql = "SELECT A.* FROM eshop_spider.goods A,eshop_spider.goods_category B WHERE a.`category_id` = b.`ID` AND b.`NAME3` = ?";

        list = this.getJdbcTempate().queryForList(t_sql, val);

        String sql_b = "SELECT params FROM czzfcg_ss.es_goods_type where name = ?";

        List<Map<String, Object>> list_b = this.getJdbcTempate().queryForList(sql_b, key);

        for (Map<String, Object> map : list) {

            String __id = (String) map.get("id");
            String __realid = map.get("real_id").toString();
            String __name = (String) map.get("name");
            String __price = (String) map.get("price");
            String __imgurl = (String) map.get("image_url");
            String img_name = __imgurl.substring(__imgurl.lastIndexOf("/") + 1);
            String img_suffix = img_name.substring(img_name.lastIndexOf("."),img_name.length());
            String __realPath_original = "fs:/attachment/goods/" + __realid + "/" + __id +  img_suffix;
            String ll_sql = "SELECT B.* FROM eshop_spider.goods A,eshop_spider.`goods_params` B WHERE a.`ID` = b.`GOODS_ID` AND a.`ID` = ? ORDER BY b.`ID`";

            List<Map<String, Object>> __list = this.getJdbcTempate().queryForList(ll_sql, __id);

            Map<String, GoodsParam> __pMap = new LinkedHashMap<String, GoodsParam>();

            String __brand_name = null;

            System.out.println(__id);

            for (Map<String, Object> __map : __list) {
                String name1 = StringUtils.trim((String) __map.get("name1"));
                String name2 = StringUtils.trim((String) __map.get("name2"));
                String value = StringUtils.trim((String) __map.get("value"));

                if (name2.equals("品牌")) {
                    __brand_name = value;
                    continue;
                }
                Integer isSameName1 = 0;
                Integer isSameName2 = 0;
                try {
                    List<Map<String, Object>> list_le = JsonUtils.fromJson(list_b.get(0).get("params").toString(), List.class);
                    if (list_le != null && list_le.size() > 0) {
                        for (Map<String, Object> le : list_le) {
                            List<Map<String, Object>> list_params = (List<Map<String, Object>>) le.get("paramList");
                            if (name1.equals(le.get("name").toString())) {
                                isSameName1 = 1;
                            }
                            for (Map<String, Object> le1 : list_params) {
                                if (name2.equals(le1.get("name").toString())) {
                                    isSameName2 = 1;
                                }
                            }
                        }
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
                if (isSameName1 > 0 && isSameName2 > 0) {
                    GoodsParam gP = __pMap.get(name1);

                    if (gP == null) {
                        gP = new GoodsParam();
                        gP.setName(name1);
                        __pMap.put(name1, gP);
                    }

                    gP.getParamList().add(new GoodsParam.SubParam(name2, value));
                }
            }

            String __paramsJson = JsonUtils.toJson(__pMap.values());
            Integer brand_id = 31;

            Integer cat_id = 6;
            Integer type_id = 3;
            String goods_sql = "insert into czzfcg_ss.es_goods (goods_id,name,sn,price,brand_id,type_id,params,thumbnail,big,small,original,cost,mktprice,cat_id,market_enable,disabled,have_spec,view_count,buy_count,store,enable_store,point,buy_num,comment_num) values (:goods_id,:name,:sn,:price,:brand_id,:type_id,:params,:thumbnail,:big,:small,:original,0,:price,:cat_id,:market_enable,:disabled,:have_spec,:view_count,:buy_count,:store,:enable_store,:point,:buy_num,:comment_num)";

            Map<String, Object> paramMap = new HashMap<String, Object>();
            paramMap.put("goods_id", __realid);
            paramMap.put("name", __name);
            paramMap.put("sn", __id);
            paramMap.put("price", __price);
            paramMap.put("brand_id", brand_id);
            paramMap.put("cat_id", cat_id);
            paramMap.put("type_id", type_id);
            paramMap.put("params", __paramsJson);
//            paramMap.put("thumbnail", __imgurl);
            paramMap.put("thumbnail", __realPath_original);
            paramMap.put("big", __realPath_original);
            paramMap.put("small", __realPath_original);
            paramMap.put("original", __realPath_original);
            paramMap.put("market_enable", 1);
            paramMap.put("disabled", 0);
            paramMap.put("have_spec", 0);
            paramMap.put("view_count", 0);
            paramMap.put("buy_count", 0);
            paramMap.put("store", 0);
            paramMap.put("enable_store", 0);
            paramMap.put("point", 0);
            paramMap.put("buy_num", 0);
            paramMap.put("comment_num", 0);

            this.getNamedParameterJdbcTemplate().update(goods_sql, paramMap);

            Map realMap = new HashMap();
            realMap.put("goods_id",__id);
            realMap.put("real_id",__realid);
            realMap.put("sn",__id);
            updateProductAndGood(realMap);
        }

        String goods_list_sql = "select * from czzfcg_ss.es_goods order by goods_id ";

        List<Map<String, Object>> goodsList = this.getJdbcTempate().queryForList(goods_list_sql);

        for (Map<String, Object> goodsMap : goodsList) {
            Map<String, Object> specMap = new LinkedHashMap<String, Object>();

            specMap.put("cost", goodsMap.get("cost"));
            specMap.put("enable_store", goodsMap.get("enable_store"));
            specMap.put("goodsLvPrices", new String[]{});
            specMap.put("goods_id", goodsMap.get("goods_id"));
            specMap.put("name", goodsMap.get("name"));
            specMap.put("price", goodsMap.get("price"));
            specMap.put("product_id", goodsMap.get("goods_id"));
            specMap.put("sn", goodsMap.get("sn"));
            specMap.put("specList", new String[]{});
            specMap.put("specs", "");
            specMap.put("specsvIdJson", "[]");
            specMap.put("store", goodsMap.get("store"));
            specMap.put("weight", goodsMap.get("weight"));

            String jsonStr = "[" + JsonUtils.toJson(specMap) + "]";

            this.getJdbcTempate().update("update czzfcg_ss.es_goods set specs = ? where goods_id = ?", jsonStr, goodsMap.get("goods_id"));
        }
    }

    public JdbcTemplate getJdbcTempate() {
        return jdbcTempate;
    }

    public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() {
        return new NamedParameterJdbcTemplate(getJdbcTempate());
    }

    // 去除数组中重复的记录
    public String[] array_unique(String[] a) {
        // array_unique
        List<String> list = new LinkedList<String>();
        for (int i = 0; i < a.length; i++) {
            if (!list.contains(a[i])) {
                list.add(a[i]);
            }
        }
        return (String[]) list.toArray(new String[list.size()]);
    }

}
